package db.queries.core;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import core.common.Configurations;
import core.objects.ObjectWithID;
import db.ICrossInsert;
import db.IQueryBuilder;
import db.dbTables.AbstractCrossDBTable;
import db.dbTables.AbstractDBTable;
import db.dbTables.AuthorTable;
import db.dbTables.BookCommentTable;
import db.dbTables.BookCrossAuthorTable;
import db.dbTables.BookCrossCategoryTable;
import db.dbTables.BookCrossPublisherTable;
import db.dbTables.BookTable;
import db.dbTables.CategoryTable;
import db.dbTables.CityTable;
import db.dbTables.CountryTable;
import db.dbTables.DbColumn;
import db.dbTables.LoanTable;
import db.dbTables.PublisherTable;
import db.dbTables.UserCommentTable;
import db.dbTables.UserCrossBookTable;
import db.dbTables.UserTable;

public class QueriesEnvironment {

    private static AuthorTable authorTable = null;
    private static BookTable bookTable = null;
    private static BookCommentTable bookCommentTable = null;
    private static BookCrossAuthorTable bookCrossAuthorTable = null;
    private static BookCrossCategoryTable bookCrossCategoryTable = null;
    private static BookCrossPublisherTable bookCrossPublisherTable = null;
    private static CategoryTable categoryTable = null;
    private static CityTable cityTable = null;
    private static CountryTable countryTable = null;
    private static LoanTable loanTable = null;
    private static PublisherTable publisherTable = null;
    private static UserCommentTable userCommentTable = null;
    private static UserCrossBookTable userCrossBookTable = null;
    private static UserTable userTable = null;

    //don't allow instantiation of this class:
    private QueriesEnvironment() {
    }

    public static AuthorTable getAuthorTable() {
        if (authorTable == null) {
            authorTable = new AuthorTable();
        }
        return authorTable;
    }

    public static BookTable getBookTable() {
        if (bookTable == null) {
            bookTable = new BookTable();
        }
        return bookTable;
    }

    public static BookCommentTable getBookCommentTable() {
        if (bookCommentTable == null) {
            bookCommentTable = new BookCommentTable();
        }
        return bookCommentTable;
    }

    public static BookCrossAuthorTable getBookCrossAuthorTable() {
        if (bookCrossAuthorTable == null) {
            bookCrossAuthorTable = new BookCrossAuthorTable();
        }
        return bookCrossAuthorTable;
    }

    public static BookCrossCategoryTable getBookCrossCategoryTable() {
        if (bookCrossCategoryTable == null) {
            bookCrossCategoryTable = new BookCrossCategoryTable();
        }
        return bookCrossCategoryTable;
    }

    public static BookCrossPublisherTable getBookCrossPublisherTable() {
        if (bookCrossPublisherTable == null) {
            bookCrossPublisherTable = new BookCrossPublisherTable();
        }
        return bookCrossPublisherTable;
    }

    public static CategoryTable getCategoryTable() {
        if (categoryTable == null) {
            categoryTable = new CategoryTable();
        }
        return categoryTable;
    }

    public static CityTable getCityTable() {
        if (cityTable == null) {
            cityTable = new CityTable();
        }
        return cityTable;
    }

    public static CountryTable getCountryTable() {
        if (countryTable == null) {
            countryTable = new CountryTable();
        }
        return countryTable;
    }

    public static LoanTable getLoanTable() {
        if (loanTable == null) {
            loanTable = new LoanTable();
        }
        return loanTable;
    }

    public static PublisherTable getPublisherTable() {
        if (publisherTable == null) {
            publisherTable = new PublisherTable();
        }
        return publisherTable;
    }

    public static UserCommentTable getUserCommentTable() {
        if (userCommentTable == null) {
            userCommentTable = new UserCommentTable();
        }
        return userCommentTable;
    }

    public static UserCrossBookTable getUserCrossBookTable() {
    	if (userCrossBookTable == null) {
    		userCrossBookTable = new UserCrossBookTable();
    	}
    	return userCrossBookTable;
    }
    
    public static UserTable getUserTable() {
        if (userTable == null) {
            userTable = new UserTable();
        }
        return userTable;
    }
    
    /**
     * Generates a string of all the tables names which contain the DBColumns given
     * @param dbcArray
     * @return 
     */
    private static String genTablesString(DbColumn[] dbcArray) {
        if (dbcArray == null || dbcArray.length == 0) {
            return "";
        }
        Set<AbstractDBTable> tablesSet = new HashSet<AbstractDBTable>();
        StringBuilder sb = new StringBuilder("");
        for (DbColumn dbc : dbcArray) {
            if (!tablesSet.contains(dbc.getParentTable())) {
                sb.append("`" + Configurations.getDbSchema() + "`.`").append(dbc.getParentTable().getName()).append("`,");
                tablesSet.add(dbc.getParentTable());
            }
        }
        return sb.substring(0, sb.length() - 1);
    }

    /**
     * Generates a string of all the columns names given
     * @param dbcArray
     * @return 
     */
    private static String genColumnsString(DbColumn[] dbcArray) {
        if (dbcArray == null || dbcArray.length == 0) {
            return "";
        }
        StringBuilder sb = new StringBuilder("");
        for (DbColumn dbc : dbcArray) {
            sb.append(dbc.getName()).append(",");
        }
        return sb.substring(0, sb.length() - 1);
    }

    /**
     * Generates a string in the format "(?,?,...,?)" to be used when constructing a prepared statement
     * @param args
     * @return 
     */
    private static String genValuesPattern(int args) {
        if (args == 0) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < args; i++) {
            sb.append(i == 0 ? "?" : ",?");
        }
        return sb.toString();
    }
        
    /**
     * Generates a pattern of an INSERT statement to be used when constructing a prepared statement
     * @param dbcArray
     * @return 
     */
    public static String genInsertPattern(DbColumn[] dbcArray) {
        String Str =  "INSERT INTO " + genTablesString(dbcArray) + " (" + genColumnsString(dbcArray) + ") VALUES(" + genValuesPattern(dbcArray.length) + ")";
        return Str;
               
    }

    /**
     * Returns a string of a SHOW ALL statement for a given table
     * @param table
     * @return 
     */
    public static IQueryBuilder showAllColumnsQuery(final AbstractDBTable table) {
        return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {

			}
			
			@Override
			public String buildQuery() {
				return "SHOW COLUMNS FROM " + Configurations.getDbSchema() + "." + table.getName() + ";";
			}
		};
    }

    /**
     * Returns a string of a SELECT statement which counts the number of occurences of strToSearch in table containing column dbc
     * @param dbc - the column
     * @param strToSearch
     * @return 
     */
    public static IQueryBuilder selectCountQuery(final DbColumn dbc, final String strToSearch) {

    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				statement.setString(1, strToSearch);
			}
			
			@Override
			public String buildQuery() {
				return "SELECT count(*) FROM " + Configurations.getDbSchema() + "." + dbc.getParentTable().getName() + " WHERE " + dbc.getName() + " = ?;";
			}
		};
    }

    /**
     * Returns a string of a SELECT statement which counts the number of occurences of strToSearch in table containing column dbc
     * @param dbc - the column
     * @param strToSearch
     * @return 
     */
    public static IQueryBuilder selectCountQuery(final DbColumn dbc, final int idToSearch) {

        return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				statement.setInt(1, idToSearch);
			}
			
			@Override
			public String buildQuery() {
				return "SELECT count(*) FROM " + Configurations.getDbSchema() + "." + dbc.getParentTable().getName() + " WHERE " + dbc.getName() + " = ?;";
			}
		};
    }
    
    /**
     * Adds objects of where clause (both column names and values) to the
     * preparedStatements parameterized '?'
     *
     * @param statement The Prepared Statement
     * @param values Object[] array with column values in the where clause
     * @param start Start index of the prepared statement to insert into
     */
    private static void insertValuesToPreparedStatement(
    		PreparedStatement statement, Object[] values, int start) throws SQLException {
    	
    	if (values == null)
    		return;
    	
		for (int i = 0; i < values.length; i++) {
			Object o = values[i];
			if (o == null) {
				statement.setNull(i+start, Types.NULL);
			} else if (o instanceof String) {
				statement.setString(i+start, (String)o);
			} else if (o instanceof Integer) {
				statement.setInt(i+start, (Integer)o);
			} else if (o instanceof Float) {
				statement.setFloat(i+start, (Float)o);
			} else if (o instanceof Boolean) {
				statement.setBoolean(i+start, (Boolean)o);
			} else if (o instanceof Date) {
				statement.setDate(i+start, (Date)o);
			}
		}
    }
    
    /**
     * Returns a string of a SELECT statement which selects the given columns from their tables.  
     * @param dbcArray - columns array
     * @return 
     */
    public static IQueryBuilder selectQuery(String whereStr, Object[] whereValues, DbColumn[] dbcArray) {
    	return selectQuery(whereStr, whereValues, dbcArray, false);
    }
    
    /**
     * Returns a string of a SELECT statement which selects the given columns from their tables.  
     * @param dbcArray - columns array
     * @return 
     */
    public static IQueryBuilder selectQuery(final String whereStr, final Object[] whereValues, final DbColumn[] dbcArray, final boolean limitSearch) {

    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				insertValuesToPreparedStatement(statement, whereValues, 1);
			}
			
			@Override
			public String buildQuery() {
				return "SELECT "
						+ genColumnsString(dbcArray) + " FROM " + genTablesString(dbcArray)
						+ ((whereValues != null && !whereValues.equals("")) ? " WHERE " + whereStr : "")
						+ (limitSearch ? " LIMIT " + Configurations.getBookSearchLimit() + " " : "") + ";";
			}
		};
        
    }

    /**
     * Returns a string of a SELECT statement which selects the given columns from their tables, based
     * on a cross specification.
     * 
     * @param dbcArray - columns array
     * @param table - instance of cross table on which the cross query operates
     * @param tableToSelect - which of the two tables the cross table connects between to select
     * @return 
     */
    public static IQueryBuilder selectCrossQuery(String whereStr,
    		Object[] whereValues, AbstractCrossDBTable table,
    		AbstractDBTable tableToSelect) {
    	return selectCrossQuery(whereStr, whereValues, table, tableToSelect, false);
    }
    
    /**
     * Returns a string of a SELECT statement which selects the given columns from their tables, based
     * on a cross specification.
     * 
     * @param dbcArray - columns array
     * @param table - instance of cross table on which the cross query operates
     * @param tableToSelect - which of the two tables the cross table connects between to select
     * @return 
     */
    public static IQueryBuilder selectCrossQuery(final String whereStr,
    		final Object[] whereValues, final AbstractCrossDBTable table,
    		final AbstractDBTable tableToSelect, final boolean limitSearch) {
        
        return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				insertValuesToPreparedStatement(statement, whereValues, 1);
			}
			
			@Override
			public String buildQuery() {
				return "SELECT DISTINCT "
		        		+ genColumnsString(tableToSelect.getDbColumns().toArray(new DbColumn[0]))
		        		// WHERE cross_table.table1_id = table1.id AND cross_table.table2_id = table2.id:
		        		+ " FROM " + table.getName() + ", "
		        		+ table.getTable1().getName() + ","
		        		+ table.getTable2().getName()
		        		+ " WHERE " + table.getTable1IDColumn().getName() + " = " + table.getTable1().getIDColumn().getName()
		        		+ " AND " + table.getTable2IDColumn().getName() + " = " + table.getTable2().getIDColumn().getName()
		        		+ ((whereValues != null && !whereValues.equals("")) ? " AND " + whereStr : "")
						+ (limitSearch ? " LIMIT " + Configurations.getBookSearchLimit() + " " : "") + ";";
			}
		};
    }
 
    public static IQueryBuilder selectAvgQuery(final DbColumn column, final DbColumn whereColumn, final int whereValue) {

    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				statement.setInt(1, whereValue);
			}
			
			@Override
			public String buildQuery() {
				return "SELECT AVG(" + column.getName() + ") FROM "
						+ column.getParentTable().getName()
						+ " WHERE " + whereColumn.getName() + " = ?;";
			}
		};
    }
    
    /**
     * updates the collection of objects with respect to the given table
     * (executes for each one "UPDATE table SET columns = values WHERE table.getIDColumn() = obj.getID()")
     */
    public static IQueryBuilder doUpdate(final Collection<? extends ObjectWithID> objs, final AbstractDBTable table) {
    	
    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				
				for (ObjectWithID obj : objs) {
					Object[] values = obj.toObjectArray();
					List<DbColumn> columns = table.getDbColumns();
					columns.remove(table.getIDColumn());
					for (int i = 0; i < columns.size(); i++) {
						DbColumn column = columns.get(i);
						column.setPreparedStatmentArgument(
								statement, i+1, values[i]);
					}
					statement.setInt(values.length + 1, obj.getID());
					statement.addBatch();
				}
			}
			
			@Override
			public String buildQuery() {

				StringBuilder update = new StringBuilder();
				update.append("UPDATE " + table.getName() + " SET ");

				List<DbColumn> columns = table.getDbColumns();
				columns.remove(table.getIDColumn());
				boolean firstArgument = true;
				for (DbColumn column : columns) {
					if (!firstArgument) {
						update.append(",");
					}
					update.append(column.getName() + " = ?");
					firstArgument = false;
				}
				update.append(" WHERE " + table.getIDColumn().getName() + " = ?;");
				return update.toString();
			}
		};
    	
    }
    
    public static IQueryBuilder doDelete(final Collection<? extends ObjectWithID> objs, final AbstractDBTable table) {
    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				for (ObjectWithID obj : objs) {
					statement.setInt(1, obj.getID());
					statement.addBatch();
				}
			}
			
			@Override
			public String buildQuery() {
				return "DELETE FROM " + table.getName()
						+ " WHERE " + table.getIDColumn().getName() + " = ?;";
			}
		};
    }
    
    public static IQueryBuilder doCrossDelete(final ObjectWithID obj, final Collection<? extends ObjectWithID> objs, final AbstractCrossDBTable table) {
    	
    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				for (ObjectWithID o : objs) {
					statement.setInt(1, obj.getID());
					statement.setInt(2, o.getID());
					statement.addBatch();
				}
			}
			
			@Override
			public String buildQuery() {
				return "DELETE FROM " + table.getName()
						+ " WHERE " + table.getTable1IDColumn().getName() + " = ? "
						+ " AND " + table.getTable2IDColumn().getName() + " = ?;";
			}
		};
    	
    }
    
    public static IQueryBuilder buildCrossBatchQuery(final AbstractCrossDBTable table, final Collection<? extends ObjectWithID> objs, final ICrossInsert cross) {
    	
    	return new IQueryBuilder() {
			
			@Override
			public void setParams(PreparedStatement statement) throws SQLException {
				for (ObjectWithID obj : objs) {

					Collection<? extends ObjectWithID> crossObjs = cross.getCrossObjects(obj);
					
					if (crossObjs == null)
						continue;
					
					for (ObjectWithID crossObj : crossObjs) {
						statement.setInt(1, obj.getID());
						statement.setInt(2, crossObj.getID());
						statement.addBatch();
					}
				}
			}
			
			@Override
			public String buildQuery() {
				List<DbColumn> columns = table.getDbColumns();
				columns.remove(table.getIDColumn());
				return genInsertPattern(columns.toArray(new DbColumn[0]));
			}
		};
    }
    
    /** 
     * Add the given collection to the preparedStatement. 
     * @param preSt - Prepared statement
     * @param dbcArray - columns array
     * @param objSet - Base object collection
     * @throws SQLException 
     */
    public static void addBaseObjectsToPreSt(PreparedStatement preSt, DbColumn[] dbcArray, Collection<? extends ObjectWithID> objSet) throws SQLException {
        int count = 1;
        for (ObjectWithID obj : objSet) {
            obj.setId(count);
            count++;
            for (int i = 0; i < dbcArray.length; i++) {
                dbcArray[i].setPreparedStatmentArgument(preSt, i + 1, obj.toObjectArray()[i]);
            }
            try {
                preSt.addBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /** 
     * Adds the members Ids of the given collection to the given preparedStatement 
     * @param preSt
     * @param dbcArray
     * @param objSet 
     */
    public static void addBaseObjectsIdsToPreSt(PreparedStatement preSt, DbColumn[] dbcArray, Collection<? extends ObjectWithID> objSet) {
        for (ObjectWithID obj : objSet) {
            try {
                dbcArray[0].setPreparedStatmentArgument(preSt, 1, obj.getID());
                preSt.addBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
}
